package school.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import school.entity.Student;
import util.DBUtil;

public class StudentDao {
	public static final StudentDao DAO = new StudentDao();
	public static final int PAGESIZE = 20;// 页大小

	public int add(Student s) {
		String sql = "insert into student (fromcity,wechat,address,sname,sex,telephone,gradeid,birthday,pass) values (?,?,?,?,?,?,?,str_to_date(?,'%Y-%m-%d'),?)";
		Connection con = DBUtil.getConnection();
		PreparedStatement st = null;
		try {
			int index = 1;
			st = con.prepareStatement(sql);
			st.setString(index++, s.getFromcity());
			st.setString(index++, s.getWechat());
			st.setString(index++, s.getAddr());
			st.setString(index++, s.getSname());
			st.setString(index++, s.getSex());
			st.setString(index++, s.getTelephone());
			st.setLong(index++, s.getGradeid());
			st.setObject(index++, s.getBirthday());
			st.setString(index++, s.getPass());
			int rs = st.executeUpdate();
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(st);
			DBUtil.close(con);
		}

		return 0;
	}

	// 根据学号删除
	public int del(int stuno) {
		String str = "delete from student where stuno=?";
		int k = util.DBUtil.executeUpdate(str, stuno);
		return k;
	}

	// 修改学生
	public int update(Student s) {
		String sql = "update student set sname=?,fromcity=?,sex=?,address=?,enterdate=?,birthday=?,telephone=?  where stuno=?";
		return DBUtil.executeUpdate(sql, s.getSname(), s.getFromcity(),
				s.getSex(), s.getAddr(), s.getEnterDate(), s.getBirthday(),
				s.getTelephone(), s.getStuno());
	}

	// 根据学号读取学生对象
	public Student get(int stuno) {
		String sql = "select * from student where stuno=?";
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, stuno);
			rs = ps.executeQuery();
			if (rs.next()) {
				Student s = new Student();
				s.setStuno(stuno);
				s.setSname(rs.getString("sname"));
				s.setTelephone(rs.getString("telephone"));
				s.setWechat(rs.getString("wechat"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				// ...还有省略
				return s;
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, ps, con);
		}
		return null;
	}

	/**
	 * 根据页号返回分页的数据 .即使找不到，返回一个空集合，不返回null
	 * 
	 * @param pageNo
	 * @return
	 */
	public List<Student> search(int pageNo) {
		String sql = "select * from student limit ?,?";
		int start = (pageNo - 1) * PAGESIZE;
		List<Student> datas = new java.util.ArrayList<Student>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, PAGESIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setTelephone(rs.getString("telephone"));
				s.setWechat(rs.getString("wechat"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				// ...还有省略
				datas.add(s);// !!!!!!!!!!!!!!!
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, ps, con);
		}
		return datas;
	}

	/**
	 * 根据姓名模糊查找，如果没有，则忽略； 根据年级精确查找，如果为null,则忽略
	 * 
	 * @param pageNo
	 * @param sname
	 * @param gradeId
	 * @return
	 */
	public List<Student> paging(int pageNo, String sname, Integer gradeId) {

		// String sql = "select * from student where sname like '%" + sname
		// + "%' and gradeId=? limit ?,?";
		List<Object> params = new ArrayList<Object>();// 专门存放参数
		String sql = "select * from student where 1=1";
		if (!DBUtil.isBlank(sname)) {
			sql += " and sname like ?";
			params.add("%" + sname + "%");
		}
		// >>>>>>>>>params.toArray();

		if (!DBUtil.isBlank(gradeId)) {
			sql += " and gradeId=?";
			params.add(gradeId);
		}
		sql += " limit ?,?";
		// sname like ? and gradeId=? limit ?,?";

		int start = (pageNo - 1) * PAGESIZE;
		params.add(start);
		params.add(PAGESIZE);
		System.out.println(sql + ">>>" + params);
		List<Student> datas = new java.util.ArrayList<Student>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}

			/*
			 * ps.setString(1, "%" + sname + "%"); ps.setInt(2, gradeId);
			 * ps.setInt(3, start); ps.setInt(4, PAGESIZE);
			 */
			rs = ps.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setTelephone(rs.getString("telephone"));
				s.setWechat(rs.getString("wechat"));
				s.setFromcity(rs.getString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				// ...还有省略
				datas.add(s);// !!!!!!!!!!!!!!!
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, ps, con);
		}
		return datas;
	}
}
